﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class sqlFieldEdit : System.Web.UI.Page
{

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            string tblName = Request["tblName"];
            loadData(tblName);
        }

    }


    private void loadData(string tblname)
    {
        string colname = Request["colname"];
        string tblTitle = Request["memo"];

        lblTableName.Text = tblname;
        txtTblName.Text = tblname;
        lblFieldName.Text = colname;
        txtFieldName.Text = colname;

        if (tblTitle != "")
        {

            txtTblTitle.Text = tblTitle;
        }
    }


    private void setFieldMemo(string tblname, string colName, string memo)
    {
        string sql = @"SELECT Isnull(m.VALUE,'0') AS memo, x.colname, x.tblname FROM (SELECT o.name AS tblname, c.name AS colname, t.name AS typename, c.length AS fdlength, c.isnullable AS [isnull], c.colid, o.id AS oid FROM syscolumns AS c, systypes AS t, sysobjects AS o WHERE o.id = c.id AND o.xtype = 'u' AND o.status >= 0 AND t.xtype = c.xtype and t.name!='sysname') AS x LEFT JOIN sys.extended_properties AS m ON m.major_id = x.oid AND x.colid = m.minor_id where x.colname='" + colName + "' and x.tblname='" + tblname + "'";

        //sql = sql.Replace("appDataPort", tblname);
        iData.DataAcess worker = new iData.DataAcess();
        string val = worker.ExecuteScalar(sql);
        //int rowsCount = worker.ExecuteNonQuery(sql);
        if (val != "0")
        {
            //delete memo
            sql = "EXEC sp_dropextendedproperty 'MS_Description','user',dbo,'table','" + tblname + "','column'," + colName + "";
            //sql = sql.Replace("appDataPort", tblname);
            worker.ExecuteNonQuery(sql);
        }

        sql = "exec sp_addextendedproperty N'MS_Description',N'" + memo + "',N'user',N'dbo',N'table',N'" + tblname + "',N'column',N'" + colName + "'";
        //sql = sql.Replace("appDataPort", tblname);
        worker.ExecuteNonQuery(sql);

    }

    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        string tblTitle = txtTblTitle.Text;
        string tblName = txtTblName.Text;
        string colName = txtFieldName.Text;
        setFieldMemo(tblName, colName, tblTitle);
        LIB.JsWrite.alertAndCloseUpdateParent("修改成功");
    }
}